package srdb.migration;

import java.io.*;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.Map;
import java.util.Properties;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.log4j.Logger;

import srdb.migration.common.MTKConstants;

import com.edb.commons.db.Keywords;

public class Utility {
    private static String validOracleIdentifierPattern = "^[a-zA-Z]([a-zA-Z0-9_#$])*$";

    private static String validEDBIdentifierPattern = "^[a-zA-Z_]([a-zA-Z0-9_$#])*+$";

    private static final Hashtable<String, String> edbtoOraDefaultDateClause = new Hashtable();
    private static final Hashtable<String, String> oratoSrdbDefaultDateClause = new Hashtable();
    public static final Hashtable<String, String> ora2srType = new Hashtable();


    public static String formatDate(Date date, String formatPattern) {
        if (formatPattern == null) {
            formatPattern = "MMM dd, yyyy hh:mm:ss";
        }

        SimpleDateFormat sdf = new SimpleDateFormat(formatPattern);

        return sdf.format(date);
    }

    public static void saveProperties(Properties prop, String propComments, String filePath)
            throws FileNotFoundException, IOException {
        FileOutputStream fos = new FileOutputStream(getTempPath() + filePath);
        prop.store(fos, propComments);
        fos.close();
    }

    public static String getTempPath() {
        String tmpFilePath = null;

        if (System.getProperty("repTempPath") != null)
            tmpFilePath = System.getProperty("repTempPath");
        else {
            tmpFilePath = System.getProperty("java.io.tmpdir");
        }

        if (!tmpFilePath.endsWith(File.separator)) {
            tmpFilePath = tmpFilePath + File.separator;
        }

        return tmpFilePath;
    }

    public static boolean isValidOracleIdentifier(String Identifier) {
        return Identifier.matches(validOracleIdentifierPattern);
    }

    public static boolean isValidEDBIdentifier(String identifier) {
        return (identifier.matches(validEDBIdentifierPattern)) && (!Keywords.isReservedKeyword(identifier));
    }

    public static boolean hasValidEDBIdentifierPattern(String identifier) {
        return identifier.matches(validEDBIdentifierPattern);
    }


    public static boolean isCaseSensitive(String identifierName, MTKConstants.DATABASE_TYPE dbType) {
        if (identifierName == null) {
            return false;
        }
        switch (MTKConstants.DATABASE_TYPE.values()[dbType.ordinal()]) {
            case ENTERPRISEDB:
            case SRDB:
                if ((identifierName.equals(identifierName.toLowerCase())) && (isValidEDBIdentifier(identifierName)))
                    break;
                return true;
            case ORACLE:
                if ((identifierName.equals(identifierName.toUpperCase())) && (isValidOracleIdentifier(identifierName)))
                    break;
                return true;
            default:
                return false;
        }
        return false;
    }

    public static void main(String[] args) {
        System.out.println(isCaseSensitive("HR", MTKConstants.DATABASE_TYPE.ORACLE));
    }

    public static String getValidFileNamePrefixFor(String schemaName, String tableName) {
        String fileNamePrefix = schemaName + "." + tableName;

        fileNamePrefix = fileNamePrefix.replaceAll("\\\\", "");
        fileNamePrefix = fileNamePrefix.replaceAll("/", "");
        fileNamePrefix = fileNamePrefix.replaceAll(":", "");
        fileNamePrefix = fileNamePrefix.replaceAll("\\?", "");
        fileNamePrefix = fileNamePrefix.replaceAll("\\\"", "");
        fileNamePrefix = fileNamePrefix.replaceAll("<", "");
        fileNamePrefix = fileNamePrefix.replaceAll(">", "");
        fileNamePrefix = fileNamePrefix.replaceAll("\\|", "");
        return fileNamePrefix;
    }

    public static String escape(String identifierName, MTKConstants.DATABASE_TYPE dbType) {
        String value = identifierName;
        switch (MTKConstants.DATABASE_TYPE.values()[dbType.ordinal()]) {
//     case 1:
//     case 2:
//     case 3:
//       if ((!isCaseSensitive(identifierName, dbType)) || 
//         (identifierName.startsWith("\"")) || (identifierName.endsWith("\""))) break;
//       value = "\"" + value + "\""; break;
            case SRDB:
                value = "`" + value + "`";
                break;
//     case 5:
//       if ((value.startsWith("[")) && (value.endsWith("]"))) break;
//       value = "[" + value + "]";
        }

        return value;
    }

    public static String convertToDefaultCase(String identifierName, MTKConstants.DATABASE_TYPE targetDbType) {
        switch (MTKConstants.DATABASE_TYPE.values()[targetDbType.ordinal()]) {
            case SRDB:
            case MYSQL:
                return identifierName.toLowerCase();
            case ORACLE:
                return identifierName.toUpperCase();
        }
        return identifierName;
    }

    public static String getTargetIdentifierNameInDefaultCase(MTKConstants.DATABASE_TYPE sourceDbType, MTKConstants.DATABASE_TYPE targetDbType, String sourceIdentifierName) {
        String targetIdentifierName = sourceIdentifierName;
        if (!isCaseSensitive(sourceIdentifierName, sourceDbType)) {
            targetIdentifierName = convertToDefaultCase(targetIdentifierName, targetDbType);
        }

        return targetIdentifierName;
    }

    public static SQLException processException(Exception exp) {
        String message = exp.getMessage();
        if (message.indexOf("STACKTRACE:") > 0) {
            message = message.substring(0, message.indexOf("STACKTRACE:"));
        }

        return new SQLException(message);
    }

    public static String removeEnclosingQuotes(String identifierName) {
        if ((identifierName == null) || (identifierName.length() == 0)) {
            return identifierName;
        }

        String nonQuotedName = identifierName;

        if (nonQuotedName.substring(0, 1).equals("\"")) {
            nonQuotedName = nonQuotedName.replaceFirst("\"", "");
            nonQuotedName = nonQuotedName.substring(0, nonQuotedName.length() - 1);
        }

        return nonQuotedName;
    }

    public static Logger getLogger() {
        return Logger.getRootLogger();
    }

    public static String[] parseOracleURL(String url) {
        String[] urlInfo = new String[3];

        if (url.contains("@//")) {
            String[] temp = url.split("@//");

            String[] temp2 = temp[1].split("/");
            urlInfo[2] = temp2[1];

            String[] temp3 = temp2[0].split(":");
            urlInfo[0] = temp3[0];
            urlInfo[1] = temp3[1];
        } else {
            String[] temp = url.split("@");

            if ((temp != null) && (temp.length == 2)) {
                String[] token = temp[1].split(":");

                if ((token != null) && (token.length == 3)) {
                    urlInfo[0] = token[0];
                    urlInfo[1] = token[1];
                    urlInfo[2] = token[2];
                }
            }
        }

        return urlInfo;
    }

    public static String hasDBlinkPasswords(String[] optionValues, String name) {
        for (int i = 0; i < optionValues.length; i++) {
            String[] token = optionValues[i].split("=");
            if ((token != null) && (token.length == 2) &&
                    (token[0].equals(name))) {
                return token[1];
            }

        }

        return null;
    }

    public static boolean isValidExecuteable(String execName) {
        try {
            Process p = Runtime.getRuntime().exec(execName);
            p.destroy();
            return true;
        } catch (Exception exp) {
        }
        return false;
    }

    public static String convertEDBDateDCtoOraDateDC(String clause) {
        return (String) edbtoOraDefaultDateClause.get(clause);
    }

    public static String convertOraDateDCtoSrdbDateDC(String clause) {
        return (String) oratoSrdbDefaultDateClause.get(clause.toLowerCase());
    }


    public static Map getNPSCredentials(Connection conn, String schema)
            throws SQLException {
        HashMap map = new HashMap();
        String query = "SELECT regexp_replace(connStr, '=.*', '') \"Param\", regexp_replace(connStr, '.*=', '') \"Value\" FROM regexp_split_to_table((select dblink_getConnStr((select nspforeignserver from pg_namespace where nspname='" + schema + "'),(select usesysid from pg_user where usename=current_user))), ';') connStr;";
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(query);
            while (rs.next()) {
                map.put(rs.getString(1).toLowerCase(), rs.getString(2));
            }
            return map;
        } finally {

            if (rs != null) {
                rs.close();
            }

            if (stmt != null)
                stmt.close();
        }
    }

    public static String readGUC(Connection connection, String gucName)
            throws SQLException {
//     Statement stmt = null;
//     ResultSet rs = null;
//     try
//     {
//       stmt = connection.createStatement();
//       String query = "SHOW " + gucName + ";";
//       rs = stmt.executeQuery(query);
// 
//       if (rs.next()) {
//         str1 = rs.getString(1); jsr 23;
//       }
// 
//       str1 = null;
//     }
//     finally
//     {
//       String str1;
//       if (rs != null) {
//         rs.close();
//       }
//       if (stmt != null)
//         stmt.close();
//     }

        return null;
    }

    public static String formatMySQLZeroDateTimeToPG(String dateTimeValue)
            throws ParseException {
        if (dateTimeValue == null)
            return null;
        String DateTimePattern;
        if (dateTimeValue.equalsIgnoreCase("0000-00-00")) {
            DateTimePattern = "yyyy-MM-dd";
        } else {
            if (dateTimeValue.equalsIgnoreCase("0000-00-00 00:00:00"))
                DateTimePattern = "yyyy-MM-dd HH:mm:ss";
            else
                return dateTimeValue;
        }
        SimpleDateFormat DATETIME_FORMAT = new SimpleDateFormat(DateTimePattern);
        Date date = DATETIME_FORMAT.parse(dateTimeValue);
        dateTimeValue = DATETIME_FORMAT.format(date);
        return dateTimeValue;
    }

    public static boolean isSQLServerVersion2005()
            throws SQLException {
        MigrationToolkit mtk = MigrationToolkit.getInstance();

        if (mtk.offlineMigration()) {
            return false;
        }

        return mtk.getTargetConn().getConnection().getMetaData().getDatabaseMajorVersion() == 9;
    }

    public static String bytesToHexString(byte[] data) {
        StringBuffer sb = new StringBuffer();
        for (byte b : data) {
            sb.append(Integer.toString((b & 0xFF) + 256, 16).substring(1));
        }
        return sb.toString();
    }

    public static String bytesToHexString(Blob blob) throws SQLException, IOException {
        StringBuffer sb = new StringBuffer();
        InputStream is = blob.getBinaryStream();
        byte[] b = new byte[512];
        int size = 0;
        while ((size = is.read(b)) > 0) {
            for (int i = 0; i < size; i++) {
                sb.append(Integer.toString((b[i] & 0xFF) + 256, 16).substring(1));
            }
        }

        return sb.toString();
    }

    /**
     * replaceAll,忽略大小写
     * Add by kevin 2015年04月28日16:13:24
     *
     * @param input
     * @param regex
     * @param replacement
     * @return
     */
    public static String replaceAll(String input, String regex, String replacement) {
        Pattern p = Pattern.compile(regex, Pattern.CASE_INSENSITIVE);
        Matcher m = p.matcher(input);
        String result = m.replaceAll(replacement);
        return result;
    }

    static {
        edbtoOraDefaultDateClause.put("(clock_timestamp())::timestamp(0) without time zone", "sysdate");
        edbtoOraDefaultDateClause.put("('now'::text)::date", "current_date");
        edbtoOraDefaultDateClause.put("now()", "sysdate");

        oratoSrdbDefaultDateClause.put("sysdate", "current_date");
        oratoSrdbDefaultDateClause.put("sysdate \n\t", "current_date");

        ora2srType.put("NUMBER", "numeric");
        // CHAR types limit of 2000 bytes with defaults to 1 if no length
        // is specified. PG char type has max length set to 8104 so it
        // should match all needs
        ora2srType.put("CHAR", "char");
        ora2srType.put("NCHAR", "char");
        // VARCHAR types the limit is 2000 bytes in Oracle 7 and 4000 in
        // Oracle 8. PG varchar type has max length iset to 8104 so it
        // should match all needs
        ora2srType.put("VARCHAR", "varchar");
        ora2srType.put("NVARCHAR", "varchar");
        ora2srType.put("VARCHAR2", "varchar");
        ora2srType.put("NVARCHAR2", "varchar");
        // The DATE data type is used to store the date and time
        // information. PG type timestamp should match all needs.
        ora2srType.put("DATE", "timestamp");
        // Type LONG is like VARCHAR2 but with up to 2Gb. PG type text
        // should match all needs or if you want you could use blob
        ora2srType.put("LONG", "text"); // Character data of variable length
        ora2srType.put("LONG RAW", "bytea");
        // Types LOB and FILE are like LONG but with up to 4Gb. PG type
        // text should match all needs or if you want you could use blob
        // (large object)
        ora2srType.put("CLOB", "text"); // A large object containing single-byte characters
        ora2srType.put("NCLOB", "text"); // A large object containing national character set data
        ora2srType.put("BLOB", "bytea"); // Binary large object
        // The full path to the external file is returned if destination type is text.
        // If the destination type is bytea the content of the external file is returned.
        ora2srType.put("BFILE", "bytea"); // Locator for external large binary file
        // The RAW type is presented as hexadecimal characters. The
        // contents are treated as binary data. Limit of 2000 bytes
        // PG type text should match all needs or if you want you could
        // use blob (large object)
        ora2srType.put("RAW", "bytea");
        ora2srType.put("ROWID", "oid");
        ora2srType.put("FLOAT", "double precision");
        ora2srType.put("DEC", "decimal");
        ora2srType.put("DECIMAL", "decimal");
        ora2srType.put("DOUBLE PRECISION", "double precision");
        ora2srType.put("INT", "integer");
        ora2srType.put("INTEGER", "integer");
        ora2srType.put("BINARY_INTEGER", "integer");
        ora2srType.put("PLS_INTEGER", "integer");
        ora2srType.put("REAL", "real");
        ora2srType.put("SMALLINT", "smallint");
        ora2srType.put("BINARY_FLOAT", "double precision");
        ora2srType.put("BINARY_DOUBLE", "double precision");
        ora2srType.put("TIMESTAMP", "timestamp");
        ora2srType.put("BOOLEAN", "boolean");
        ora2srType.put("INTERVAL", "interval");
        ora2srType.put("XMLTYPE", "xml");
        ora2srType.put("TIMESTAMP WITH TIME ZONE", "timestamp with time zone");
        ora2srType.put("TIMESTAMP WITH LOCAL TIME ZONE", "timestamp with time zone");
        ora2srType.put("SDO_GEOMETRY", "geometry");


    }

    /**
     * 追加内容到文件
     *
     * @param fileName
     * @param content
     * @date 2015-07-21
     */
    public static void appendFile(String fileName, String content) {
        try {
            File file=new File(fileName);
            if(!file.exists())
            {
                try {
                    file.createNewFile();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            //打开一个写文件器，构造函数中的第二个参数true表示以追加形式写文件
            FileWriter writer = new FileWriter(fileName, true);
            writer.write(content);
            writer.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

/* 

 * 
 */